from typing import Optional

from numpy import delete
from sqlalchemy import func, or_, select, update
from sqlalchemy.ext.asyncio import AsyncSession

from app.exceptions.base import AppException
from app.models.system import SysDict, SysDictItem
from app.schemas.base import Pagination
from app.schemas.dict import DictForm, DictItemForm, DictItemPageVO, DictPageVO


class DictService:

    def __init__(self, session: AsyncSession):
        self.session = session

    async def get_dict_page(
        self,
        page_num: int = 1,
        page_size: int = 10,
        keywords: Optional[str] = None,
    ) -> Pagination[DictPageVO]:
        """
        分页查询字典（优化版）

        Args:
            page_num: 页码，最小为1
            page_size: 每页记录数，范围1-100
            keywords: 搜索关键词，支持字典名称和编码模糊搜索

        Returns:
            Pagination[DictPageVO]: 分页结果

        Raises:
            DatabaseError: 数据库查询异常
        """
        # 构建基础查询
        stmt = select(SysDict).where(SysDict.is_deleted == 0)

        # 关键词搜索
        if keywords and keywords.strip():
            keyword = keywords.strip()
            stmt = stmt.where(
                or_(
                    SysDict.name.ilike(f"%{keyword}%"),  # 使用ilike忽略大小写
                    SysDict.dict_code.ilike(f"%{keyword}%"),
                )
            )

        # 高效计数
        count_stmt = select(func.count()).select_from(stmt.subquery().alias("subq"))
        result = await self.session.execute(count_stmt)
        total = result.scalar_one()

        # 无数据直接返回
        if total == 0:
            return Pagination(list=[], total=0, page_num=page_num, page_size=page_size)

        # 分页查询
        stmt = stmt.offset((page_num - 1) * page_size).limit(page_size).order_by(SysDict.create_time.desc())

        # 执行查询
        dicts_result = await self.session.execute(stmt)
        dicts = dicts_result.scalars().all()

        # 转换为VO对象
        dict_vo_list = [DictPageVO.model_validate(dict_obj) for dict_obj in dicts]

        return Pagination(
            list=dict_vo_list,
            total=total,
            page_num=page_num,
            page_size=page_size,
        )

    async def get_dict_list(self) -> list[SysDict]:
        """
        获取所有字典的下拉选项列表
        Returns:
            list[StringOption]: 字典选项列表
        Raises:
            DatabaseError: 数据库查询异常
        """
        stmt = select(SysDict).where(SysDict.status == 1, SysDict.is_deleted == 0)
        result = await self.session.execute(stmt)
        dicts = result.scalars().all()
        return dicts

    async def get_dict_by_id(self, id: int) -> SysDict:
        """
        根据ID获取字典
        Args:
            id: 字典ID
        Returns:
            SysDict: 字典对象
        Raises:
            DatabaseError: 数据库查询异常
        """
        stmt = select(SysDict).where(SysDict.id == id, SysDict.is_deleted == 0)
        result = await self.session.execute(stmt)
        dict_ = result.scalar_one_or_none()
        return dict_

    async def get_dict_item_page(
        self, page_num: int = 1, page_size: int = 10, keywords: Optional[str] = None, dict_code: Optional[str] = None
    ) -> Pagination[DictItemPageVO]:
        """
        分页查询字典项（优化版）
        Args:
            page_num: 页码，最小为1
            page_size: 每页记录数，范围1-100
            keywords: 搜索关键词，支持字典项值和名称模糊搜索
            dict_code: 字典编码
        Returns:
            Pagination[DictItemPageVO]: 分页结果
        Raises:
            DatabaseError: 数据库查询异常
        """
        # 构建基础查询
        stmt = select(SysDictItem).where(SysDictItem.status == 1)
        if dict_code:
            stmt = stmt.where(SysDictItem.dict_code == dict_code)
        # 关键词搜索
        if keywords and keywords.strip():
            keyword = keywords.strip()
            stmt = stmt.where(
                or_(
                    SysDictItem.value.ilike(f"%{keyword}%"),  # 使用ilike忽略大小写
                    SysDictItem.label.ilike(f"%{keyword}%"),
                )
            )

        # 高效计数
        count_stmt = select(func.count()).select_from(stmt.subquery().alias("subq"))
        result = await self.session.execute(count_stmt)
        total = result.scalar_one()
        # 无数据直接返回
        if total == 0:
            return Pagination(list=[], total=0, page_num=page_num, page_size=page_size)
        # 分页查询
        stmt = stmt.offset((page_num - 1) * page_size).limit(page_size).order_by(SysDictItem.create_time.desc())
        # 执行查询
        dict_items_result = await self.session.execute(stmt)
        dict_items = dict_items_result.scalars().all()
        # 转换为VO对象
        dict_item_vo_list = [DictItemPageVO.model_validate(dict_item) for dict_item in dict_items]
        return Pagination(
            list=dict_item_vo_list,
            total=total,
            page_num=page_num,
            page_size=page_size,
        )

    async def get_dict_item_list(self, dict_code: str) -> list[SysDictItem]:
        """
        获取所有字典项的下拉选项列表
        Args:
            dict_code: 字典编码
        Returns:
            list[StringOption]: 字典项选项列表
        Raises:
            DatabaseError: 数据库查询异常
        """
        stmt = (
            select(SysDictItem)
            .where(SysDictItem.status == 1, SysDictItem.dict_code == dict_code)
            .order_by(SysDictItem.sort.asc())
        )
        result = await self.session.execute(stmt)
        dict_items = result.scalars().all()
        return dict_items

    async def get_dict_item_by_id(self, id: int) -> SysDictItem:
        """
        根据ID获取字典项
        Args:
            id: 字典项ID
        Returns:
            SysDictItem: 字典项对象
        Raises:
            DatabaseError: 数据库查询异常
        """
        stmt = select(SysDictItem).where(SysDictItem.id == id)
        result = await self.session.execute(stmt)
        dict_item = result.scalar_one_or_none()
        return dict_item

    async def get_dict_by_code(self, dict_code: str) -> SysDict:
        """
        根据字典编码获取字典
        Args:
            dict_code: 字典编码
        Returns:
            SysDict: 字典对象
        Raises:
            DatabaseError: 数据库查询异常
        """
        stmt = select(SysDict).where(SysDict.dict_code == dict_code, SysDict.is_deleted == 0)
        result = await self.session.execute(stmt)
        dict_ = result.scalar_one_or_none()
        return dict_

    async def update_dict(self, dict_in: DictForm) -> SysDict:
        """
        更新字典
        Args:
            dict_in: 字典对象
        Returns:
            SysDict: 更新后的字典对象
        Raises:
            DatabaseError: 数据库操作异常
            AppException: 业务逻辑异常
        """
        # 检查字典是否存在
        dict = await self.get_dict_by_id(dict_in.id)
        if not dict:
            raise AppException(message=f"字典[{dict_in.id}]不存在")

        # 保存旧的字典编码用于后续判断
        dict_code_old = dict.dict_code

        # 检查字典编码是否重复（排除当前字典且编码有变化时）
        if dict_code_old != dict_in.dict_code:
            dict_ = await self.get_dict_by_code(dict_in.dict_code)
            if dict_ and dict_.id != dict_in.id:
                raise AppException(message=f"字典编码[{dict_in.dict_code}]已存在")

        # 更新字典属性（排除id字段）
        for key, value in dict_in.model_dump(exclude_unset=True).items():
            if key in ["id"]:
                continue
            setattr(dict, key, value)

        # 如果字典编码改变，更新关联的字典项
        if dict_code_old != dict_in.dict_code:
            stmt = update(SysDictItem).where(SysDictItem.dict_code == dict_code_old).values(dict_code=dict_in.dict_code)
            await self.session.execute(stmt)

        # 刷新对象状态
        await self.session.commit()
        await self.session.refresh(dict)
        return dict

    async def save_dict(self, dict_in: DictForm) -> SysDict:
        """
        新增字典项
        Args:
            dict_in: 字典项对象
        Returns:
            SysDict: 新增后的字典项对象
        Raises:
            DatabaseError: 数据库操作异常
        """
        dict_ = SysDict.model_validate(dict_in)
        if self.get_dict_by_code(dict_.dict_code):
            raise AppException(message=f"字典编码[{dict_.dict_code}]已存在")
        self.session.add(dict_)
        await self.session.commit()
        return dict_

    async def save_dict_item(self, dict_form: DictItemForm) -> SysDictItem:
        """
        新增字典项
        Args:
            dict_form: 字典项对象
        Returns:
            SysDictItem: 新增后的字典项对象
        Raises:
            DatabaseError: 数据库操作异常
        """
        dict_item = SysDictItem.model_validate(dict_form)
        self.session.add(dict_item)
        await self.session.commit()
        return dict_item

    async def delete_dict_by_ids(self, ids: list[int]):
        """
        根据ID删除字典
        Args:
            ids: 字典ID列表
        Returns:
            None
        Raises:
            DatabaseError: 数据库操作异常
        """
        stmt = select(SysDict).where(SysDict.id.in_(ids))
        result = await self.session.execute(stmt)
        dicts = result.scalars().all()
        dict_codes = [dict_.dict_code for dict_ in dicts]
        if dict_codes:
            # 删除字典项
            stmt = delete(SysDictItem).where(SysDictItem.dict_code.in_(dict_codes))
            await self.session.execute(stmt)
        # 删除字典
        stmt = delete(SysDict).where(SysDict.id.in_(ids))
        await self.session.execute(stmt)
        await self.session.commit()

    async def update_dict_item(self, dict_item_in: DictItemForm) -> SysDictItem:
        """
        更新字典项
        Args:
            dict_item_in: 字典项对象
        Returns:
            SysDictItem: 更新后的字典项对象
        Raises:
            DatabaseError: 数据库操作异常
            AppException: 业务逻辑异常
        """
        # 检查字典项是否存在
        dict_item = await self.get_dict_item_by_id(dict_item_in.id)
        if not dict_item:
            raise AppException(message=f"字典项[{dict_item_in.dict_code}]不存在")
        stmt = (
            update(SysDictItem)
            .where(SysDictItem.id == dict_item_in.id)
            .values(**dict_item_in.model_dump(exclude_unset=True, exclude=["id"]))
        )
        await self.session.execute(stmt)
        await self.session.commit()
        return dict_item

    async def delete_dict_item_by_ids(self, ids: list[int]):
        """
        根据ID删除字典项
        Args:
            ids: 字典项ID列表
        Returns:
            None
        Raises:
            DatabaseError: 数据库操作异常
        """
        stmt = delete(SysDictItem).where(SysDictItem.id.in_(ids))
        await self.session.execute(stmt)
        await self.session.commit()
